package DAL.JDBC;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import DAL.Entites.Order;

public class OrderJDBC {
	
	Connection conn;
	String tableName = "orders"; 
	
	public OrderJDBC(Connection connection) {
		this.conn = connection;
	}
	
	public int insert(Date Created, int UserId, int CreditCard, String Feedback) {
		// this is for selecting db incase we do insert first
		// ((DriverManagerDataSource)dataSource).setUrl("jdbc:mysql://localhost:3306/TestData");
		int inserted = 0;
		String SQL = String.format("insert into %s values(null, '2013-12-12' ,%s,%s,'%s')",
				tableName, UserId, CreditCard, Feedback);
		
		try {
			Statement st = this.conn.createStatement();
			inserted = st.executeUpdate(SQL, Statement.RETURN_GENERATED_KEYS);
			System.out.println("Created Record");
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return inserted;
	}
	
	private Order mapRow(ResultSet rs) throws SQLException {
		Order order = new Order(rs.getInt("Id"));
		order.setCreated(rs.getDate("Created"));
		order.setUserId(rs.getInt("UserId"));
		order.setCreditCard(rs.getString("CreditCard"));
		order.setFeedback(rs.getString("Feedback"));
		System.out.println("mapRow");

		return order;
	}
	
	public List<Order> listOrders() {
		List<Order> ordersList = new ArrayList<Order>();
		String SQL = String.format("select * from %s", tableName);
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				ordersList.add(mapRow(rs));
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return ordersList;
	}
	
	public List<Order> listOrdersByUserId(int UserId) {
		List<Order> ordersList = new ArrayList<Order>();
		String SQL = String.format("select * from %s where userId = %s", tableName, UserId);
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				ordersList.add(mapRow(rs));
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return ordersList;
	}
}
